// Licensed to the Apache Software Foundation (ASF) under one
// or more contributor license agreements.  See the NOTICE file
// distributed with this work for additional information
// regarding copyright ownership.  The ASF licenses this file
// to you under the Apache License, Version 2.0 (the
// "License"); you may not use this file except in compliance
// with the License.  You may obtain a copy of the License at
//
//   http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing,
// software distributed under the License is distributed on an
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
// KIND, either express or implied.  See the License for the
// specific language governing permissions and limitations
// under the License.

package org.apache.doris.nereids.datasets.tpch;

import org.apache.doris.utframe.TestWithFeService;

import java.util.ArrayList;
import java.util.List;

public class TPCHUtils {

    public static final List<String> SQLS = new ArrayList<String>() {
        {
            add(Q1);
            add(Q2);
            add(Q3);
            add(Q4);
            add(Q5);
            add(Q6);
            add(Q7);
            add(Q8);
            add(Q9);
            add(Q10);
            add(Q11);
            add(Q12);
            add(Q13);
            add(Q14);
            add(Q15);
            add(Q16);
            add(Q17);
            add(Q18);
            add(Q19);
            add(Q20);
            add(Q21);
            add(Q22);
        }
    };

    public static final String Q1 = "select\n"
            + "    l_returnflag,\n"
            + "    l_linestatus,\n"
            + "    sum(l_quantity) as sum_qty,\n"
            + "    sum(l_extendedprice) as sum_base_price,\n"
            + "    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,\n"
            + "    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,\n"
            + "    avg(l_quantity) as avg_qty,\n"
            + "    avg(l_extendedprice) as avg_price,\n"
            + "    avg(l_discount) as avg_disc,\n"
            + "    count(*) as count_order\n"
            + "from\n"
            + "    lineitem\n"
            + "where\n"
            + "    l_shipdate <= date '1998-12-01' - interval '90' day\n"
            + "group by\n"
            + "    l_returnflag,\n"
            + "    l_linestatus\n"
            + "order by\n"
            + "    l_returnflag,\n"
            + "    l_linestatus;";

    public static final String Q2 = "select\n"
            + "        s_acctbal,\n"
            + "        s_name,\n"
            + "        n_name,\n"
            + "        p_partkey,\n"
            + "        p_mfgr,\n"
            + "        s_address,\n"
            + "        s_phone,\n"
            + "        s_comment\n"
            + "from\n"
            + "        part,\n"
            + "        supplier,\n"
            + "        partsupp,\n"
            + "        nation,\n"
            + "        region\n"
            + "where\n"
            + "        p_partkey = ps_partkey\n"
            + "        and s_suppkey = ps_suppkey\n"
            + "        and p_size = 15\n"
            + "        and p_type like '%BRASS'\n"
            + "        and s_nationkey = n_nationkey\n"
            + "        and n_regionkey = r_regionkey\n"
            + "        and r_name = 'EUROPE'\n"
            + "        and ps_supplycost = (\n"
            + "                select\n"
            + "                        min(ps_supplycost)\n"
            + "                from\n"
            + "                        partsupp,\n"
            + "                        supplier,\n"
            + "                        nation,\n"
            + "                        region\n"
            + "                where\n"
            + "                        p_partkey = ps_partkey\n"
            + "                        and s_suppkey = ps_suppkey\n"
            + "                        and s_nationkey = n_nationkey\n"
            + "                        and n_regionkey = r_regionkey\n"
            + "                        and r_name = 'EUROPE'\n"
            + "        )\n"
            + "order by\n"
            + "        s_acctbal desc,\n"
            + "        n_name,\n"
            + "        s_name,\n"
            + "        p_partkey\n"
            + "limit 100;";

    public static final String Q2_rewrite = "select\n"
            + "    s_acctbal,\n"
            + "    s_name,\n"
            + "    n_name,\n"
            + "    p_partkey,\n"
            + "    p_mfgr,\n"
            + "    s_address,\n"
            + "    s_phone,\n"
            + "    s_comment\n"
            + "from\n"
            + "partsupp,\n"
            + "(\n"
            + "  select ps_partkey, min(ps_supplycost) as ps_s from\n"
            + "  partsupp, supplier, nation, region\n"
            + "  where s_suppkey = ps_suppkey\n"
            + "    and s_nationkey = n_nationkey\n"
            + "    and n_regionkey = r_regionkey\n"
            + "    and r_name = 'EUROPE'\n"
            + "  group by ps_partkey\n"
            + ") t1,\n"
            + "supplier,\n"
            + "part,\n"
            + "nation,\n"
            + "region\n"
            + "where p_partkey = t1.ps_partkey\n"
            + "    and p_partkey = partsupp.ps_partkey\n"
            + "    and s_suppkey = ps_suppkey\n"
            + "    and p_size = 15\n"
            + "    and p_type like '%BRASS'\n"
            + "    and s_nationkey = n_nationkey\n"
            + "    and n_regionkey = r_regionkey\n"
            + "    and r_name = 'EUROPE'\n"
            + "    and ps_supplycost = t1.ps_s\n"
            + "order by\n"
            + "    s_acctbal desc,\n"
            + "    n_name,\n"
            + "    s_name,\n"
            + "    p_partkey\n"
            + "limit 100;";

    public static String Q3 = "select\n"
            + "        l_orderkey,\n"
            + "        sum(l_extendedprice * (1 - l_discount)) as revenue,\n"
            + "        o_orderdate,\n"
            + "        o_shippriority\n"
            + "from\n"
            + "        customer,\n"
            + "        orders,\n"
            + "        lineitem\n"
            + "where\n"
            + "        c_mktsegment = 'BUILDING'\n"
            + "        and c_custkey = o_custkey\n"
            + "        and l_orderkey = o_orderkey\n"
            + "        and o_orderdate < date '1995-03-15'\n"
            + "        and l_shipdate > date '1995-03-15'\n"
            + "group by\n"
            + "        l_orderkey,\n"
            + "        o_orderdate,\n"
            + "        o_shippriority\n"
            + "order by\n"
            + "        revenue desc,\n"
            + "        o_orderdate\n"
            + "limit 10;";

    public static String Q3_rewrite = "select \n"
            + "    l_orderkey,\n"
            + "    sum(l_extendedprice * (1 - l_discount)) as revenue,\n"
            + "    o_orderdate,\n"
            + "    o_shippriority\n"
            + "from\n"
            + "    (\n"
            + "        select l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey from\n"
            + "        lineitem join orders\n"
            + "        where l_orderkey = o_orderkey\n"
            + "        and o_orderdate < date '1995-03-15'\n"
            + "        and l_shipdate > date '1995-03-15'\n"
            + "    ) t1 join customer c \n"
            + "    on c.c_custkey = t1.o_custkey\n"
            + "    where c_mktsegment = 'BUILDING'\n"
            + "group by\n"
            + "    l_orderkey,\n"
            + "    o_orderdate,\n"
            + "    o_shippriority\n"
            + "order by\n"
            + "    revenue desc,\n"
            + "    o_orderdate\n"
            + "limit 10;";

    public static String Q4 = "select\n"
            + "        o_orderpriority,\n"
            + "        count(*) as order_count\n"
            + "from\n"
            + "        orders\n"
            + "where\n"
            + "        o_orderdate >= date '1993-07-01'\n"
            + "        and o_orderdate < date '1993-10-01'\n"
            + "        and exists (\n"
            + "                select\n"
            + "                        *\n"
            + "                from\n"
            + "                        lineitem\n"
            + "                where\n"
            + "                        l_orderkey = o_orderkey\n"
            + "                        and l_commitdate < l_receiptdate\n"
            + "        )\n"
            + "group by\n"
            + "        o_orderpriority\n"
            + "order by\n"
            + "        o_orderpriority;";

    public static String Q4_rewrite = "select \n"
            + "    o_orderpriority,\n"
            + "    count(*) as order_count\n"
            + "from\n"
            + "    (\n"
            + "        select\n"
            + "            *\n"
            + "        from\n"
            + "            lineitem\n"
            + "        where l_commitdate < l_receiptdate\n"
            + "    ) t1\n"
            + "    right semi join orders\n"
            + "    on t1.l_orderkey = o_orderkey\n"
            + "where\n"
            + "    o_orderdate >= date '1993-07-01'\n"
            + "    and o_orderdate < date '1993-07-01' + interval '3' month\n"
            + "group by\n"
            + "    o_orderpriority\n"
            + "order by\n"
            + "    o_orderpriority;";

    public static String Q5 = "select\n"
            + "        n_name,\n"
            + "        sum(l_extendedprice * (1 - l_discount)) as revenue\n"
            + "from\n"
            + "        customer,\n"
            + "        orders,\n"
            + "        lineitem,\n"
            + "        supplier,\n"
            + "        nation,\n"
            + "        region\n"
            + "where\n"
            + "        c_custkey = o_custkey\n"
            + "        and l_orderkey = o_orderkey\n"
            + "        and l_suppkey = s_suppkey\n"
            + "        and c_nationkey = s_nationkey\n"
            + "        and s_nationkey = n_nationkey\n"
            + "        and n_regionkey = r_regionkey\n"
            + "        and r_name = 'ASIA'\n"
            + "        and o_orderdate >= date '1994-01-01'\n"
            + "        and o_orderdate < date '1995-01-01'\n"
            + "group by\n"
            + "        n_name\n"
            + "order by\n"
            + "        revenue desc;";

    public static String Q6 = "select\n"
            + "        sum(l_extendedprice * l_discount) as revenue\n"
            + "from\n"
            + "        lineitem\n"
            + "where\n"
            + "        l_shipdate >= date '1994-01-01'\n"
            + "        and l_shipdate < date '1995-01-01'\n"
            + "        and l_discount between 0.06 - 0.01 and 0.06 + 0.01\n"
            + "        and l_quantity < 24;";

    public static String Q7 = "select\n"
            + "        supp_nation,\n"
            + "        cust_nation,\n"
            + "        l_year,\n"
            + "        sum(volume) as revenue\n"
            + "from\n"
            + "        (\n"
            + "                select\n"
            + "                        n1.n_name as supp_nation,\n"
            + "                        n2.n_name as cust_nation,\n"
            + "                        extract(year from l_shipdate) as l_year,\n"
            + "                        l_extendedprice * (1 - l_discount) as volume\n"
            + "                from\n"
            + "                        supplier,\n"
            + "                        lineitem,\n"
            + "                        orders,\n"
            + "                        customer,\n"
            + "                        nation n1,\n"
            + "                        nation n2\n"
            + "                where\n"
            + "                        s_suppkey = l_suppkey\n"
            + "                        and o_orderkey = l_orderkey\n"
            + "                        and c_custkey = o_custkey\n"
            + "                        and s_nationkey = n1.n_nationkey\n"
            + "                        and c_nationkey = n2.n_nationkey\n"
            + "                        and (\n"
            + "                                (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')\n"
            + "                                or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')\n"
            + "                        )\n"
            + "                        and l_shipdate between date '1995-01-01' and date '1996-12-31'\n"
            + "        ) as shipping\n"
            + "group by\n"
            + "        supp_nation,\n"
            + "        cust_nation,\n"
            + "        l_year\n"
            + "order by\n"
            + "        supp_nation,\n"
            + "        cust_nation,\n"
            + "        l_year;";

    public static final String Q8 = "select\n"
            + "        o_year,\n"
            + "        sum(case\n"
            + "                when nation = 'BRAZIL' then volume\n"
            + "                else 0\n"
            + "        end) / sum(volume) as mkt_share\n"
            + "from\n"
            + "        (\n"
            + "                select\n"
            + "                        extract(year from o_orderdate) as o_year,\n"
            + "                        l_extendedprice * (1 - l_discount) as volume,\n"
            + "                        n2.n_name as nation\n"
            + "                from\n"
            + "                        part,\n"
            + "                        supplier,\n"
            + "                        lineitem,\n"
            + "                        orders,\n"
            + "                        customer,\n"
            + "                        nation n1,\n"
            + "                        nation n2,\n"
            + "                        region\n"
            + "                where\n"
            + "                        p_partkey = l_partkey\n"
            + "                        and s_suppkey = l_suppkey\n"
            + "                        and l_orderkey = o_orderkey\n"
            + "                        and o_custkey = c_custkey\n"
            + "                        and c_nationkey = n1.n_nationkey\n"
            + "                        and n1.n_regionkey = r_regionkey\n"
            + "                        and r_name = 'AMERICA'\n"
            + "                        and s_nationkey = n2.n_nationkey\n"
            + "                        and o_orderdate between date '1995-01-01' and date '1996-12-31'\n"
            + "                        and p_type = 'ECONOMY ANODIZED STEEL'\n"
            + "        ) as all_nations\n"
            + "group by\n"
            + "        o_year\n"
            + "order by\n"
            + "        o_year;";

    public static final String Q9 = "select\n"
            + "        nation,\n"
            + "        o_year,\n"
            + "        sum(amount) as sum_profit\n"
            + "from\n"
            + "        (\n"
            + "                select\n"
            + "                        n_name as nation,\n"
            + "                        extract(year from o_orderdate) as o_year,\n"
            + "                        l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\n"
            + "                from\n"
            + "                        part,\n"
            + "                        supplier,\n"
            + "                        lineitem,\n"
            + "                        partsupp,\n"
            + "                        orders,\n"
            + "                        nation\n"
            + "                where\n"
            + "                        s_suppkey = l_suppkey\n"
            + "                        and ps_suppkey = l_suppkey\n"
            + "                        and ps_partkey = l_partkey\n"
            + "                        and p_partkey = l_partkey\n"
            + "                        and o_orderkey = l_orderkey\n"
            + "                        and s_nationkey = n_nationkey\n"
            + "                        and p_name like '%green%'\n"
            + "        ) as profit\n"
            + "group by\n"
            + "        nation,\n"
            + "        o_year\n"
            + "order by\n"
            + "        nation,\n"
            + "        o_year desc;";

    public static final String Q10 = "select\n"
            + "        c_custkey,\n"
            + "        c_name,\n"
            + "        sum(l_extendedprice * (1 - l_discount)) as revenue,\n"
            + "        c_acctbal,\n"
            + "        n_name,\n"
            + "        c_address,\n"
            + "        c_phone,\n"
            + "        c_comment\n"
            + "from\n"
            + "        customer,\n"
            + "        orders,\n"
            + "        lineitem,\n"
            + "        nation\n"
            + "where\n"
            + "        c_custkey = o_custkey\n"
            + "        and l_orderkey = o_orderkey\n"
            + "        and o_orderdate >= date '1993-10-01'\n"
            + "        and o_orderdate < date '1994-01-01'\n"
            + "        and l_returnflag = 'R'\n"
            + "        and c_nationkey = n_nationkey\n"
            + "group by\n"
            + "        c_custkey,\n"
            + "        c_name,\n"
            + "        c_acctbal,\n"
            + "        c_phone,\n"
            + "        n_name,\n"
            + "        c_address,\n"
            + "        c_comment\n"
            + "order by\n"
            + "        revenue desc\n"
            + "limit 20;";

    public static final String Q11 = "select \n"
            + "    ps_partkey,\n"
            + "    sum(ps_supplycost * ps_availqty) as value\n"
            + "from\n"
            + "    partsupp,\n"
            + "    supplier,\n"
            + "    nation\n"
            + "where\n"
            + "    ps_suppkey = s_suppkey\n"
            + "    and s_nationkey = n_nationkey\n"
            + "    and n_name = 'GERMANY'\n"
            + "group by\n"
            + "    ps_partkey having\n"
            + "        sum(ps_supplycost * ps_availqty) > (\n"
            + "            select\n"
            + "                sum(ps_supplycost * ps_availqty) * 0.0001000000\n"
            + "            from\n"
            + "                partsupp,\n"
            + "                supplier,\n"
            + "                nation\n"
            + "            where\n"
            + "                ps_suppkey = s_suppkey\n"
            + "                and s_nationkey = n_nationkey\n"
            + "                and n_name = 'GERMANY'\n"
            + "        )\n"
            + "order by\n"
            + "    value desc;";
    public static final String Q12 = "select\n"
            + "        l_shipmode,\n"
            + "        sum(case\n"
            + "                when o_orderpriority = '1-URGENT'\n"
            + "                        or o_orderpriority = '2-HIGH'\n"
            + "                        then 1\n"
            + "                else 0\n"
            + "        end) as high_line_count,\n"
            + "        sum(case\n"
            + "                when o_orderpriority <> '1-URGENT'\n"
            + "                        and o_orderpriority <> '2-HIGH'\n"
            + "                        then 1\n"
            + "                else 0\n"
            + "        end) as low_line_count\n"
            + "from\n"
            + "        orders,\n"
            + "        lineitem\n"
            + "where\n"
            + "        o_orderkey = l_orderkey\n"
            + "        and l_shipmode in ('MAIL', 'SHIP')\n"
            + "        and l_commitdate < l_receiptdate\n"
            + "        and l_shipdate < l_commitdate\n"
            + "        and l_receiptdate >= date '1994-01-01'\n"
            + "        and l_receiptdate < date '1995-01-01'\n"
            + "group by\n"
            + "        l_shipmode\n"
            + "order by\n"
            + "        l_shipmode;";

    public static final String Q12_rewrite = "select \n"
            + "    l_shipmode,\n"
            + "    sum(case\n"
            + "        when o_orderpriority = '1-URGENT'\n"
            + "            or o_orderpriority = '2-HIGH'\n"
            + "            then 1\n"
            + "        else 0\n"
            + "    end) as high_line_count,\n"
            + "    sum(case\n"
            + "        when o_orderpriority <> '1-URGENT'\n"
            + "            and o_orderpriority <> '2-HIGH'\n"
            + "            then 1\n"
            + "        else 0\n"
            + "    end) as low_line_count\n"
            + "from\n"
            + "    orders,\n"
            + "    lineitem\n"
            + "where\n"
            + "    o_orderkey = l_orderkey\n"
            + "    and l_shipmode in ('MAIL', 'SHIP')\n"
            + "    and l_commitdate < l_receiptdate\n"
            + "    and l_shipdate < l_commitdate\n"
            + "    and l_receiptdate >= date '1994-01-01'\n"
            + "    and l_receiptdate < date '1994-01-01' + interval '1' year\n"
            + "group by\n"
            + "    l_shipmode\n"
            + "order by\n"
            + "    l_shipmode;";

    public static final String Q13 = "select\n"
            + "        c_count,\n"
            + "        count(*) as custdist\n"
            + "from\n"
            + "        (\n"
            + "                select\n"
            + "                        c_custkey,\n"
            + "                        count(o_orderkey) c_count\n"
            + "                from\n"
            + "                        customer left outer join orders on\n"
            + "                                c_custkey = o_custkey\n"
            + "                                and o_comment not like '%special%requests%'\n"
            + "                group by\n"
            + "                        c_custkey\n"
            + "        ) as c_orders\n"
            + "group by\n"
            + "        c_count\n"
            + "order by\n"
            + "        custdist desc,\n"
            + "        c_count desc;";

    public static final String Q14 = "select\n"
            + "        100.00 * sum(case\n"
            + "                when p_type like 'PROMO%'\n"
            + "                        then l_extendedprice * (1 - l_discount)\n"
            + "                else 0\n"
            + "        end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n"
            + "from\n"
            + "        lineitem,\n"
            + "        part\n"
            + "where\n"
            + "        l_partkey = p_partkey\n"
            + "        and l_shipdate >= date '1995-09-01'\n"
            + "        and l_shipdate < date '1995-10-01';";

    public static final String Q14_rewrite = "select \n"
            + "    100.00 * sum(case\n"
            + "        when p_type like 'PROMO%'\n"
            + "            then l_extendedprice * (1 - l_discount)\n"
            + "        else 0\n"
            + "    end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n"
            + "from\n"
            + "    part,\n"
            + "    lineitem\n"
            + "where\n"
            + "    l_partkey = p_partkey\n"
            + "    and l_shipdate >= date '1995-09-01'\n"
            + "    and l_shipdate < date '1995-09-01' + interval '1' month;\n";

    public static final String Q15 = "with revenue as (\n"
            + "\tselect\n"
            + "\t\tl_suppkey as supplier_no,\n"
            + "\t\tsum(l_extendedprice * (1 - l_discount)) as total_revenue\n"
            + "\tfrom\n"
            + "\t\tlineitem\n"
            + "\twhere\n"
            + "\t\tl_shipdate >= date '1996-01-01'\n"
            + "\t\tand l_shipdate < date '1996-04-01'\n"
            + "\tgroup by\n"
            + "\t\tl_suppkey)\n"
            + "select\n"
            + "\ts_suppkey,\n"
            + "\ts_name,\n"
            + "\ts_address,\n"
            + "\ts_phone,\n"
            + "\ttotal_revenue\n"
            + "from\n"
            + "\tsupplier,\n"
            + "\trevenue\n"
            + "where\n"
            + "\ts_suppkey = supplier_no\n"
            + "\tand total_revenue = (\n"
            + "\t\tselect\n"
            + "\t\t\tmax(total_revenue)\n"
            + "\t\tfrom\n"
            + "\t\t\trevenue\n"
            + "\t)\n"
            + "order by\n"
            + "\ts_suppkey;";

    public static final String Q15_rewrite = "select \n"
            + "    s_suppkey,\n"
            + "    s_name,\n"
            + "    s_address,\n"
            + "    s_phone,\n"
            + "    total_revenue\n"
            + "from\n"
            + "    supplier,\n"
            + "    revenue0\n"
            + "where\n"
            + "    s_suppkey = supplier_no\n"
            + "    and total_revenue = (\n"
            + "        select\n"
            + "            max(total_revenue)\n"
            + "        from\n"
            + "            revenue0\n"
            + "    )\n"
            + "order by\n"
            + "    s_suppkey;";

    public static final String Q16 = "select\n"
            + "        p_brand,\n"
            + "        p_type,\n"
            + "        p_size,\n"
            + "        count(distinct ps_suppkey) as supplier_cnt\n"
            + "from\n"
            + "        partsupp,\n"
            + "        part\n"
            + "where\n"
            + "        p_partkey = ps_partkey\n"
            + "        and p_brand <> 'Brand#45'\n"
            + "        and p_type not like 'MEDIUM POLISHED%'\n"
            + "        and p_size in (49, 14, 23, 45, 19, 3, 36, 9)\n"
            + "        and ps_suppkey not in (\n"
            + "                select\n"
            + "                        s_suppkey\n"
            + "                from\n"
            + "                        supplier\n"
            + "                where\n"
            + "                        s_comment like '%Customer%Complaints%'\n"
            + "        )\n"
            + "group by\n"
            + "        p_brand,\n"
            + "        p_type,\n"
            + "        p_size\n"
            + "order by\n"
            + "        supplier_cnt desc,\n"
            + "        p_brand,\n"
            + "        p_type,\n"
            + "        p_size;";

    public static final String Q17 = "select\n"
            + "        sum(l_extendedprice) / 7.0 as avg_yearly\n"
            + "from\n"
            + "        lineitem,\n"
            + "        part\n"
            + "where\n"
            + "        p_partkey = l_partkey\n"
            + "        and p_brand = 'Brand#23'\n"
            + "        and p_container = 'MED BOX'\n"
            + "        and l_quantity < (\n"
            + "                select\n"
            + "                        0.2 * avg(l_quantity)\n"
            + "                from\n"
            + "                        lineitem\n"
            + "                where\n"
            + "                        l_partkey = p_partkey\n"
            + "        );";

    public static final String Q17_rewrite = "select \n"
            + "    sum(l_extendedprice) / 7.0 as avg_yearly\n"
            + "from\n"
            + "    lineitem join [broadcast]\n"
            + "    part p1 on p1.p_partkey = l_partkey\n"
            + "where\n"
            + "    p1.p_brand = 'Brand#23'\n"
            + "    and p1.p_container = 'MED BOX'\n"
            + "    and l_quantity < (\n"
            + "        select\n"
            + "            0.2 * avg(l_quantity)\n"
            + "        from\n"
            + "            lineitem join [broadcast]\n"
            + "            part p2 on p2.p_partkey = l_partkey\n"
            + "        where\n"
            + "            l_partkey = p1.p_partkey\n"
            + "            and p2.p_brand = 'Brand#23'\n"
            + "            and p2.p_container = 'MED BOX'\n"
            + "    );";

    public static final String Q18 = "select\n"
            + "        c_name,\n"
            + "        c_custkey,\n"
            + "        o_orderkey,\n"
            + "        o_orderdate,\n"
            + "        o_totalprice,\n"
            + "        sum(l_quantity)\n"
            + "from\n"
            + "        customer,\n"
            + "        orders,\n"
            + "        lineitem\n"
            + "where\n"
            + "        o_orderkey in (\n"
            + "                select\n"
            + "                        l_orderkey\n"
            + "                from\n"
            + "                        lineitem\n"
            + "                group by\n"
            + "                        l_orderkey having\n"
            + "                                sum(l_quantity) > 300\n"
            + "        )\n"
            + "        and c_custkey = o_custkey\n"
            + "        and o_orderkey = l_orderkey\n"
            + "group by\n"
            + "        c_name,\n"
            + "        c_custkey,\n"
            + "        o_orderkey,\n"
            + "        o_orderdate,\n"
            + "        o_totalprice\n"
            + "order by\n"
            + "        o_totalprice desc,\n"
            + "        o_orderdate\n"
            + "limit 100;";

    public static final String Q18_rewrite = "select \n"
            + "    c_name,\n"
            + "    c_custkey,\n"
            + "    t3.o_orderkey,\n"
            + "    t3.o_orderdate,\n"
            + "    t3.o_totalprice,\n"
            + "    sum(t3.l_quantity)\n"
            + "from\n"
            + "customer join\n"
            + "(\n"
            + "  select * from\n"
            + "  lineitem join\n"
            + "  (\n"
            + "    select * from\n"
            + "    orders left semi join\n"
            + "    (\n"
            + "      select\n"
            + "          l_orderkey\n"
            + "      from\n"
            + "          lineitem\n"
            + "      group by\n"
            + "          l_orderkey having sum(l_quantity) > 300\n"
            + "    ) t1\n"
            + "    on o_orderkey = t1.l_orderkey\n"
            + "  ) t2\n"
            + "  on t2.o_orderkey = l_orderkey\n"
            + ") t3\n"
            + "on c_custkey = t3.o_custkey\n"
            + "group by\n"
            + "    c_name,\n"
            + "    c_custkey,\n"
            + "    t3.o_orderkey,\n"
            + "    t3.o_orderdate,\n"
            + "    t3.o_totalprice\n"
            + "order by\n"
            + "    t3.o_totalprice desc,\n"
            + "    t3.o_orderdate\n"
            + "limit 100;";

    public static final String Q19 = "select\n"
            + "        sum(l_extendedprice* (1 - l_discount)) as revenue\n"
            + "from\n"
            + "        lineitem,\n"
            + "        part\n"
            + "where\n"
            + "        (\n"
            + "                p_partkey = l_partkey\n"
            + "                and p_brand = 'Brand#12'\n"
            + "                and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n"
            + "                and l_quantity >= 1 and l_quantity <= 1 + 10\n"
            + "                and p_size between 1 and 5\n"
            + "                and l_shipmode in ('AIR', 'AIR REG')\n"
            + "                and l_shipinstruct = 'DELIVER IN PERSON'\n"
            + "        )\n"
            + "        or\n"
            + "        (\n"
            + "                p_partkey = l_partkey\n"
            + "                and p_brand = 'Brand#23'\n"
            + "                and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n"
            + "                and l_quantity >= 10 and l_quantity <= 10 + 10\n"
            + "                and p_size between 1 and 10\n"
            + "                and l_shipmode in ('AIR', 'AIR REG')\n"
            + "                and l_shipinstruct = 'DELIVER IN PERSON'\n"
            + "        )\n"
            + "        or\n"
            + "        (\n"
            + "                p_partkey = l_partkey\n"
            + "                and p_brand = 'Brand#34'\n"
            + "                and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n"
            + "                and l_quantity >= 20 and l_quantity <= 20 + 10\n"
            + "                and p_size between 1 and 15\n"
            + "                and l_shipmode in ('AIR', 'AIR REG')\n"
            + "                and l_shipinstruct = 'DELIVER IN PERSON'\n"
            + "        );";

    public static final String Q20 = "select\n"
            + "        s_name,\n"
            + "        s_address\n"
            + "from\n"
            + "        supplier,\n"
            + "        nation\n"
            + "where\n"
            + "        s_suppkey in (\n"
            + "                select\n"
            + "                        ps_suppkey\n"
            + "                from\n"
            + "                        partsupp\n"
            + "                where\n"
            + "                        ps_partkey in (\n"
            + "                                select\n"
            + "                                        p_partkey\n"
            + "                                from\n"
            + "                                        part\n"
            + "                                where\n"
            + "                                        p_name like 'forest%'\n"
            + "                        )\n"
            + "                        and ps_availqty > (\n"
            + "                                select\n"
            + "                                        0.5 * sum(l_quantity)\n"
            + "                                from\n"
            + "                                        lineitem\n"
            + "                                where\n"
            + "                                        l_partkey = ps_partkey\n"
            + "                                        and l_suppkey = ps_suppkey\n"
            + "                                        and l_shipdate >= date '1994-01-01'\n"
            + "                                        and l_shipdate < date '1995-01-01'\n"
            + "                        )\n"
            + "        )\n"
            + "        and s_nationkey = n_nationkey\n"
            + "        and n_name = 'CANADA'\n"
            + "order by\n"
            + "        s_name;";

    public static final String Q20_rewrite = "select \n"
            + "s_name, s_address from\n"
            + "supplier left semi join\n"
            + "(\n"
            + "    select * from\n"
            + "    (\n"
            + "        select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q\n"
            + "        from lineitem\n"
            + "        where l_shipdate >= date '1994-01-01'\n"
            + "            and l_shipdate < date '1994-01-01' + interval '1' year\n"
            + "        group by l_partkey,l_suppkey\n"
            + "    ) t2 join\n"
            + "    (\n"
            + "        select ps_partkey, ps_suppkey, ps_availqty\n"
            + "        from partsupp left semi join part\n"
            + "        on ps_partkey = p_partkey and p_name like 'forest%'\n"
            + "    ) t1\n"
            + "    on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey\n"
            + "    and t1.ps_availqty > t2.l_q\n"
            + ") t3\n"
            + "on s_suppkey = t3.ps_suppkey\n"
            + "join nation\n"
            + "where s_nationkey = n_nationkey\n"
            + "    and n_name = 'CANADA'\n"
            + "order by s_name;";

    public static final String Q21 = "select\n"
            + "        s_name,\n"
            + "        count(*) as numwait\n"
            + "from\n"
            + "        supplier,\n"
            + "        lineitem l1,\n"
            + "        orders,\n"
            + "        nation\n"
            + "where\n"
            + "        s_suppkey = l1.l_suppkey\n"
            + "        and o_orderkey = l1.l_orderkey\n"
            + "        and o_orderstatus = 'F'\n"
            + "        and l1.l_receiptdate > l1.l_commitdate\n"
            + "        and exists (\n"
            + "                select\n"
            + "                        *\n"
            + "                from\n"
            + "                        lineitem l2\n"
            + "                where\n"
            + "                        l2.l_orderkey = l1.l_orderkey\n"
            + "                        and l2.l_suppkey <> l1.l_suppkey\n"
            + "        )\n"
            + "        and not exists (\n"
            + "                select\n"
            + "                        *\n"
            + "                from\n"
            + "                        lineitem l3\n"
            + "                where\n"
            + "                        l3.l_orderkey = l1.l_orderkey\n"
            + "                        and l3.l_suppkey <> l1.l_suppkey\n"
            + "                        and l3.l_receiptdate > l3.l_commitdate\n"
            + "        )\n"
            + "        and s_nationkey = n_nationkey\n"
            + "        and n_name = 'SAUDI ARABIA'\n"
            + "group by\n"
            + "        s_name\n"
            + "order by\n"
            + "        numwait desc,\n"
            + "        s_name\n"
            + "limit 100;";

    public static final String Q21_rewrite = "select \n"
            + "s_name, count(*) as numwait\n"
            + "from orders join\n"
            + "(\n"
            + "  select * from\n"
            + "  lineitem l2 right semi join\n"
            + "  (\n"
            + "    select * from\n"
            + "    lineitem l3 right anti join\n"
            + "    (\n"
            + "      select * from\n"
            + "      lineitem l1 join\n"
            + "      (\n"
            + "        select * from\n"
            + "        supplier join nation\n"
            + "        where s_nationkey = n_nationkey\n"
            + "          and n_name = 'SAUDI ARABIA'\n"
            + "      ) t1\n"
            + "      where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate\n"
            + "    ) t2\n"
            + "    on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey and l3.l_receiptdate > l3.l_commitdate\n"
            + "  ) t3\n"
            + "  on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey\n"
            + ") t4\n"
            + "on o_orderkey = t4.l_orderkey and o_orderstatus = 'F'\n"
            + "group by\n"
            + "    t4.s_name\n"
            + "order by\n"
            + "    numwait desc,\n"
            + "    t4.s_name\n"
            + "limit 100;";

    public static final String Q22 = "select\n"
            + "        cntrycode,\n"
            + "        count(*) as numcust,\n"
            + "        sum(c_acctbal) as totacctbal\n"
            + "from\n"
            + "        (\n"
            + "                select\n"
            + "                        substring(c_phone, 1, 2) as cntrycode,\n"
            + "                        c_acctbal\n"
            + "                from\n"
            + "                        customer\n"
            + "                where\n"
            + "                        substring(c_phone, 1, 2) in\n"
            + "                                ('13', '31', '23', '29', '30', '18', '17')\n"
            + "                        and c_acctbal > (\n"
            + "                                select\n"
            + "                                        avg(c_acctbal)\n"
            + "                                from\n"
            + "                                        customer\n"
            + "                                where\n"
            + "                                        c_acctbal > 0.00\n"
            + "                                        and substring(c_phone, 1, 2) in\n"
            + "                                                ('13', '31', '23', '29', '30', '18', '17')\n"
            + "                        )\n"
            + "                        and not exists (\n"
            + "                                select\n"
            + "                                        *\n"
            + "                                from\n"
            + "                                        orders\n"
            + "                                where\n"
            + "                                        o_custkey = c_custkey\n"
            + "                        )\n"
            + "        ) as custsale\n"
            + "group by\n"
            + "        cntrycode\n"
            + "order by\n"
            + "        cntrycode;";

    public static final String Q22_rewrite = "select \n"
            + "    cntrycode,\n"
            + "    count(*) as numcust,\n"
            + "    sum(c_acctbal) as totacctbal\n"
            + "from\n"
            + "    (\n"
            + "        select\n"
            + "            substring(c_phone, 1, 2) as cntrycode,\n"
            + "            c_acctbal\n"
            + "        from\n"
            + "            customer\n"
            + "        where\n"
            + "            substring(c_phone, 1, 2) in\n"
            + "                ('13', '31', '23', '29', '30', '18', '17')\n"
            + "            and c_acctbal > (\n"
            + "                select\n"
            + "                    avg(c_acctbal)\n"
            + "                from\n"
            + "                    customer\n"
            + "                where\n"
            + "                    c_acctbal > 0.00\n"
            + "                    and substring(c_phone, 1, 2) in\n"
            + "                        ('13', '31', '23', '29', '30', '18', '17')\n"
            + "            )\n"
            + "            and not exists (\n"
            + "                select\n"
            + "                    *\n"
            + "                from\n"
            + "                    orders\n"
            + "                where\n"
            + "                    o_custkey = c_custkey\n"
            + "            )\n"
            + "    ) as custsale\n"
            + "group by\n"
            + "    cntrycode\n"
            + "order by\n"
            + "    cntrycode;";

    public static void createTables(TestWithFeService service) throws Exception {
        service.createTable("CREATE TABLE lineitem (\n"
                + "    l_shipdate    DATE NOT NULL,\n"
                + "    l_orderkey    bigint NOT NULL,\n"
                + "    l_linenumber  int not null,\n"
                + "    l_partkey     int NOT NULL,\n"
                + "    l_suppkey     int not null,\n"
                + "    l_quantity    decimal(15, 2) NOT NULL,\n"
                + "    l_extendedprice  decimal(15, 2) NOT NULL,\n"
                + "    l_discount    decimal(15, 2) NOT NULL,\n"
                + "    l_tax         decimal(15, 2) NOT NULL,\n"
                + "    l_returnflag  VARCHAR(1) NOT NULL,\n"
                + "    l_linestatus  VARCHAR(1) NOT NULL,\n"
                + "    l_commitdate  DATE NOT NULL,\n"
                + "    l_receiptdate DATE NOT NULL,\n"
                + "    l_shipinstruct VARCHAR(25) NOT NULL,\n"
                + "    l_shipmode     VARCHAR(10) NOT NULL,\n"
                + "    l_comment      VARCHAR(44) NOT NULL\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`l_shipdate`, `l_orderkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\",\n"
                + "    \"colocate_with\" = \"lineitem_orders\"\n"
                + ");");

        service.createTable("CREATE TABLE orders  (\n"
                + "    o_orderkey       bigint NOT NULL,\n"
                + "    o_orderdate      DATE NOT NULL,\n"
                + "    o_custkey        int NOT NULL,\n"
                + "    o_orderstatus    VARCHAR(1) NOT NULL,\n"
                + "    o_totalprice     decimal(15, 2) NOT NULL,\n"
                + "    o_orderpriority  VARCHAR(15) NOT NULL,\n"
                + "    o_clerk          VARCHAR(15) NOT NULL,\n"
                + "    o_shippriority   int NOT NULL,\n"
                + "    o_comment        VARCHAR(79) NOT NULL\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`o_orderkey`, `o_orderdate`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\",\n"
                + "    \"colocate_with\" = \"lineitem_orders\"\n"
                + ");");

        service.createTable("CREATE TABLE partsupp (\n"
                + "    ps_partkey          int NOT NULL,\n"
                + "    ps_suppkey     int NOT NULL,\n"
                + "    ps_availqty    int NOT NULL,\n"
                + "    ps_supplycost  decimal(15, 2)  NOT NULL,\n"
                + "    ps_comment     VARCHAR(199) NOT NULL\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`ps_partkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\",\n"
                + "    \"colocate_with\" = \"part_partsupp\"\n"
                + ");");

        service.createTable("CREATE TABLE part (\n"
                + "    p_partkey          int NOT NULL,\n"
                + "    p_name        VARCHAR(55) NOT NULL,\n"
                + "    p_mfgr        VARCHAR(25) NOT NULL,\n"
                + "    p_brand       VARCHAR(10) NOT NULL,\n"
                + "    p_type        VARCHAR(25) NOT NULL,\n"
                + "    p_size        int NOT NULL,\n"
                + "    p_container   VARCHAR(10) NOT NULL,\n"
                + "    p_retailprice decimal(15, 2) NOT NULL,\n"
                + "    p_comment     VARCHAR(23) NOT NULL\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`p_partkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 24\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\",\n"
                + "    \"colocate_with\" = \"part_partsupp\"\n"
                + ");");

        service.createTable("CREATE TABLE customer (\n"
                + "    c_custkey     int NOT NULL,\n"
                + "    c_name        VARCHAR(25) NOT NULL,\n"
                + "    c_address     VARCHAR(40) NOT NULL,\n"
                + "    c_nationkey   int NOT NULL,\n"
                + "    c_phone       VARCHAR(15) NOT NULL,\n"
                + "    c_acctbal     decimal(15, 2)   NOT NULL,\n"
                + "    c_mktsegment  VARCHAR(10) NOT NULL,\n"
                + "    c_comment     VARCHAR(117) NOT NULL\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`c_custkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 24\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\"\n"
                + ");");

        service.createTable("CREATE TABLE supplier (\n"
                + "    s_suppkey       int NOT NULL,\n"
                + "    s_name        VARCHAR(25) NOT NULL,\n"
                + "    s_address     VARCHAR(40) NOT NULL,\n"
                + "    s_nationkey   int NOT NULL,\n"
                + "    s_phone       VARCHAR(15) NOT NULL,\n"
                + "    s_acctbal     decimal(15, 2) NOT NULL,\n"
                + "    s_comment     VARCHAR(101) NOT NULL\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`s_suppkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\"\n"
                + ");");

        service.createTable("CREATE TABLE `nation` (\n"
                + "  `n_nationkey` int(11) NOT NULL,\n"
                + "  `n_name`      varchar(25) NOT NULL,\n"
                + "  `n_regionkey` int(11) NOT NULL,\n"
                + "  `n_comment`   varchar(152) NULL\n"
                + ") ENGINE=OLAP\n"
                + "DUPLICATE KEY(`N_NATIONKEY`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`N_NATIONKEY`) BUCKETS 1\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\"\n"
                + ");");

        service.createTable("CREATE TABLE region  (\n"
                + "    r_regionkey      int NOT NULL,\n"
                + "    r_name       VARCHAR(25) NOT NULL,\n"
                + "    r_comment    VARCHAR(152)\n"
                + ")ENGINE=OLAP\n"
                + "DUPLICATE KEY(`r_regionkey`)\n"
                + "COMMENT \"OLAP\"\n"
                + "DISTRIBUTED BY HASH(`r_regionkey`) BUCKETS 1\n"
                + "PROPERTIES (\n"
                + "    \"replication_num\" = \"1\"\n"
                + ");");

        service.createView("create view revenue0 (supplier_no, total_revenue) as\n"
                + "select\n"
                + "    l_suppkey,\n"
                + "    sum(l_extendedprice * (1 - l_discount))\n"
                + "from\n"
                + "    lineitem\n"
                + "where\n"
                + "    l_shipdate >= date '1996-01-01'\n"
                + "    and l_shipdate < date '1996-01-01' + interval '3' month\n"
                + "group by\n"
                + "    l_suppkey;");
    }
}
